using System;
using System.Collections.Generic;
using System.Text;
using CrudI.Interface;

namespace CrudCreator.Creators
{
    public class CreateSqlInsertProcedure : AbsSqlProcedure
    {
        private CreateSqlInsertProcedure(string procedurePrefix)
            : base("Insert", procedurePrefix)
        {
            sbProcedure = new StringBuilder();             
        }
        

        /// <summary>
        /// Creates the insert procedure.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="databaseName">Name of the database.</param>
        /// <returns></returns>
        public static string GetInsertProcedure(ITable table, string databaseName, string procedurePrefix)
        {
            //const string PROCEDURE_TYPE = "Insert";

            
            CreateSqlInsertProcedure insertProc = new CreateSqlInsertProcedure(procedurePrefix);
            insertProc.procedureBegining = Environment.NewLine;
            insertProc.sbProcedure.Append(insertProc.procedureBegining + " ");
            string procedureName = table.TableName + insertProc.ProcedureType;
         // ???   //insertProcedureObject.sbProcedure.AppendLine( table.TableName + insertProcedureObject.ProcedureType);

            insertProc.sbProcedure.Append("CREATE PROCEDURE " + procedureName + Environment.NewLine);

            //insertProcedureObject.sbProcedure.AppendLine(insertProcedureObject.procedureBegining);
            insertProc.AddParameters(table);

            insertProc.BuildInsert(table);
            insertProc.procedureEnd = Environment.NewLine + " END" + Environment.NewLine; 
            insertProc.sbProcedure.Append(insertProc.procedureEnd);

            return insertProc.sbProcedure.ToString();
        }

        /// <summary>
        /// Adds the parameters to procedure.
        /// </summary>
        /// <param name="table">The table.</param>
        private void AddParameters(ITable table)
        {
            sbProcedure.AppendLine("(");
            List<IColumn> cols = table.ColumnList;
            for (int i = 0; i < cols.Count; i++)
            {
                if (cols[i].IsPK)
                    sbProcedure.Append("\tout " + base.procedurePrefix + cols[i].ColumnName + " " + cols[i].DataType + ", " + Environment.NewLine);
                else if ((i + 1) < cols.Count)
                    sbProcedure.Append("\t" + base.procedurePrefix + cols[i].ColumnName + " " + cols[i].DataType + ", " + Environment.NewLine);
                else sbProcedure.Append("\t" + base.procedurePrefix + cols[i].ColumnName + " " + cols[i].DataType);
            }
            
            sbProcedure.AppendLine(Environment.NewLine + ")");
            sbProcedure.AppendLine("BEGIN");            
        }
        
        /// <summary>
        /// Builds the insert.
        /// </summary>
        /// <param name="table">The table.</param>
        private void BuildInsert(ITable table)
        {            
            List<IColumn> cols = table.ColumnList;
            sbProcedure.Append("\tINSERT INTO " + table.TableName + "(");
            
            for (int i = 0; i < cols.Count; i++)
            {
                if (!cols[i].IsPK);
                    if ((i + 1) < cols.Count)
                        sbProcedure.Append(cols[i].ColumnName + ", ");
                    else
                        sbProcedure.AppendLine(cols[i].ColumnName + ")");
            }
   
            sbProcedure.Append("\tVALUES (");
            
            for (int i = 0; i < cols.Count; i++)
            {
                if (!cols[i].IsPK)
                    if ((i + 1) < cols.Count)
                        sbProcedure.Append(base.procedurePrefix + cols[i].ColumnName + ", ");
                    else
                        sbProcedure.AppendLine(base.procedurePrefix + cols[i].ColumnName + ");");
            }
            if (table.HasPrimaryKey)
            {
                IColumn col = table.GetPrimaryKeyField();
                string fieldName = col.ColumnName;
                sbProcedure.AppendLine("\tSET " + base.procedurePrefix + fieldName + " = SCOPE_IDENTITY();");
            }
            else
            {
                sbProcedure.AppendLine("\t/* PK NOT FOUND */"); 
                sbProcedure.AppendLine("\t/*SET pMyPkParam = LAST_INSERT_ID();*/");
            }
             
        }

    }
}
